with tmp_s03_waybill_trace_mid_dt as (
  select * from jms_dwd.dwd_s03_whole_operations_waybill_trace_mid_dt where dt >= date_add('{{ execution_date | cst_ds }}', -29)
),
tmp_dwd_oms_waybill_dt as (select waybill_no,waybill_status_code,goods_type_id,goods_type_code,
goods_type_name,goods_name,dispatch_network_code,first_code,second_code,third_code,is_sign,sign_time,
is_abnormal,waybill_source_code,waybill_source_name,is_void,is_refund,pick_network_code,pick_network_name,
input_time,input_network_code,settlement_weight,order_source_code,is_delete,package_collect_weight,package_receipt_weight,waybill_weight
,center_name,center_code,ordersource_code,ordersource_name,customer_code,customer_name
from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt where dt between date_add('{{ execution_date | cst_ds }}', -29) and '{{ execution_date | cst_ds }}'
)

insert overwrite table jms_dwd.dwd_s03_whole_operations_waybill_trace_dt partition(dt)
select * from (
select
t1.waybill_no
,t1.waybill_first_scantime
,t1.waybill_first_scantype
,t1.waybill_end_scantime
,t1.waybill_end_scantype
,t1.waybill_all_operation_times
,t1.center_all_operation_times
,t1.nodal_all_operation_times
,t1.network_all_count
,t1.center_all_count
,t1.nodal_all_count
,t1.actual_taking_scantime
,t1.actual_taking_scansitecode
,t1.actual_taking_weight
,t1.actual_taking_scantype
,t1.taking_later_scantime
,t1.taking_early_scantime
,t1.taking_early_scansitecode
,t1.taking_early_weight
,t1.warehousing_later_scantime
,t1.warehousing_early_scantime
,t1.warehousing_early_scansitecode
,t1.warehousing_early_weight
,t1.bagging_early_scantime
,t1.bagging_early_scansitecode
,t1.bagging_early_package_code
,t1.first_network_network_code
,t1.first_network_loading_later_time
,t1.first_network_loading_later_weight
,t1.first_network_send_later_time
,t1.first_network_send_later_weight
,t1.first_network_loading_send_shipment_no
,t1.first_network_loading_send_planned_departure_time
,t1.first_network_loading_send_planned_arrival_time
,t1.first_network_loading_send_actual_departure_time
,t1.first_network_loading_send_actual_arrival_time
,t1.first_network_loading_send_next_network_code
,t1.first_network_loading_send_next_network_type
,t1.first_nodal_network_code
,t1.first_nodal_unloading_early_time
,t1.first_nodal_unloading_early_weight
,t1.first_nodal_arrival_early_time
,t1.first_nodal_arrival_early_weight
,t1.first_nodal_unloading_arrival_shipment_no
,t1.first_nodal_unloading_arrival_planned_departure_time
,t1.first_nodal_unloading_arrival_planned_arrival_time
,t1.first_nodal_unloading_arrival_actual_departure_time
,t1.first_nodal_unloading_arrival_actual_arrival_time
,t1.first_nodal_loading_later_time
,t1.first_nodal_loading_later_weight
,t1.first_nodal_send_later_time
,t1.first_nodal_send_later_weight
,t1.first_nodal_loading_send_shipment_no
,t1.first_nodal_loading_send_planned_departure_time
,t1.first_nodal_loading_send_planned_arrival_time
,t1.first_nodal_loading_send_actual_departure_time
,t1.first_nodal_loading_send_actual_arrival_time
,t1.first_nodal_loading_send_next_network_code
,t1.first_nodal_loading_send_next_network_type
,t1.first_center_network_code
,t1.first_center_unloading_early_time
,t1.first_center_unloading_early_weight
,t1.first_center_arrival_early_time
,t1.first_center_arrival_early_weight
,t1.first_center_unloading_arrival_shipment_no
,t1.first_center_unloading_arrival_planned_departure_time
,t1.first_center_unloading_arrival_planned_arrival_time
,t1.first_center_unloading_arrival_actual_departure_time
,t1.first_center_unloading_arrival_actual_arrival_time
,t1.first_center_loading_later_time
,t1.first_center_loading_later_weight
,t1.first_center_send_later_time
,t1.first_center_send_later_weight
,t1.first_center_loading_send_shipment_no
,t1.first_center_loading_send_planned_departure_time
,t1.first_center_loading_send_planned_arrival_time
,t1.first_center_loading_send_actual_departure_time
,t1.first_center_loading_send_actual_arrival_time
,t1.first_center_loading_send_next_network_code
,t1.first_center_loading_send_next_network_type
,t1.second_center_network_code
,t1.second_center_unloading_early_time
,t1.second_center_unloading_early_weight
,t1.second_center_arrival_early_time
,t1.second_center_arrival_early_weight
,t1.second_center_unloading_arrival_shipment_no
,t1.second_center_unloading_arrival_planned_departure_time
,t1.second_center_unloading_arrival_planned_arrival_time
,t1.second_center_unloading_arrival_actual_departure_time
,t1.second_center_unloading_arrival_actual_arrival_time
,t1.second_center_loading_later_time
,t1.second_center_loading_later_weight
,t1.second_center_send_later_time
,t1.second_center_send_later_weight
,t1.second_center_loading_send_shipment_no
,t1.second_center_loading_send_planned_departure_time
,t1.second_center_loading_send_planned_arrival_time
,t1.second_center_loading_send_actual_departure_time
,t1.second_center_loading_send_actual_arrival_time
,t1.second_center_loading_send_next_network_code
,t1.second_center_loading_send_next_network_type
,t1.third_center_network_code
,t1.third_center_unloading_early_time
,t1.third_center_unloading_early_weight
,t1.thirdcenter_arrival_early_time
,t1.third_center_arrival_early_weight
,t1.third_center_unloading_arrival_shipment_no
,t1.third_center_unloading_arrival_planned_departure_time
,t1.third_center_unloading_arrival_planned_arrival_time
,t1.third_center_unloading_arrival_actual_departure_time
,t1.third_center_unloading_arrival_actual_arrival_time
,t1.third_center_loading_later_time
,t1.third_center_loading_later_weight
,t1.third_center_send_later_time
,t1.third_center_send_later_weight
,t1.third_center_loading_send_shipment_no
,t1.third_center_loading_send_planned_departure_time
,t1.third_center_loading_send_planned_arrival_time
,t1.third_center_loading_send_actual_departure_time
,t1.third_center_loading_send_actual_arrival_time
,t1.third_center_loading_send_next_network_code
,t1.third_center_loading_send_next_network_type
,t1.fourth_center_network_code
,t1.fourth_center_unloading_early_time
,t1.fourth_center_unloading_early_weight
,t1.fourth_center_arrival_early_time
,t1.fourth_center_arrival_early_weight
,t1.fourth_center_unloading_arrival_shipment_no
,t1.fourth_center_unloading_arrival_planned_departure_time
,t1.fourth_center_unloading_arrival_planned_arrival_time
,t1.fourth_center_unloading_arrival_actual_departure_time
,t1.fourth_center_unloading_arrival_actual_arrival_time
,t1.fourth_center_loading_later_time
,t1.fourth_center_loading_later_weight
,t1.fourth_center_send_later_time
,t1.fourth_center_send_later_weight
,t1.fourth_center_loading_send_shipment_no
,t1.fourth_center_loading_send_planned_departure_time
,t1.fourth_center_loading_send_planned_arrival_time
,t1.fourth_center_loading_send_actual_departure_time
,t1.fourth_center_loading_send_actual_arrival_time
,t1.fourth_center_loading_send_next_network_code
,t1.fourth_center_loading_send_next_network_type
,t1.end_center_network_code
,t1.end_center_unloading_early_time
,t1.end_center_unloading_early_weight
,t1.end_center_arrival_early_time
,t1.end_center_arrival_early_weight
,t1.end_center_unloading_arrival_shipment_no
,t1.end_center_unloading_arrival_planned_departure_time
,t1.end_center_unloading_arrival_planned_arrival_time
,t1.end_center_unloading_arrival_actual_departure_time
,t1.end_center_unloading_arrival_actual_arrival_time
,t1.end_center_loading_later_time
,t1.end_center_loading_later_weight
,t1.end_center_send_later_time
,t1.end_center_send_later_weight
,t1.end_center_loading_send_shipment_no
,t1.end_center_loading_send_planned_departure_time
,t1.end_center_loading_send_planned_arrival_time
,t1.end_center_loading_send_actual_departure_time
,t1.end_center_loading_send_actual_arrival_time
,t1.end_center_loading_send_next_network_code
,t1.end_center_loading_send_next_network_type
,t1.end_nodal_network_code
,t1.end_nodal_unloading_early_time
,t1.end_nodal_unloading_early_weight
,t1.end_nodal_arrival_early_time
,t1.end_nodal_arrival_early_weight
,t1.end_nodal_unloading_arrival_shipment_no
,t1.end_nodal_unloading_arrival_planned_departure_time
,t1.end_nodal_unloading_arrival_planned_arrival_time
,t1.end_nodal_unloading_arrival_actual_departure_time
,t1.end_nodal_unloading_arrival_actual_arrival_time
,t1.end_nodal_loading_later_time
,t1.end_nodal_loading_later_weight
,t1.end_nodal_send_later_time
,t1.end_nodal_send_later_weight
,t1.end_nodal_loading_send_shipment_no
,t1.end_nodal_loading_send_planned_departure_time
,t1.end_nodal_loading_send_planned_arrival_time
,t1.end_nodal_loading_send_actual_departure_time
,t1.end_nodal_loading_send_actual_arrival_time
,t1.end_nodal_loading_send_next_network_code
,t1.end_nodal_loading_send_next_network_type
,t1.end_network_network_code
,t1.end_network_unloading_early_time
,t1.end_network_arrival_early_time
,t1.deliver_early_scantime
,t1.deliver_later_scantime
,t1.deliver_later_scansitecode
,t1.deliver_later_weight
,t1.deliver_later_user
,t1.sign_later_scantime
,t1.sign_early_scantime
,t1.sign_early_scansitecode
,t1.sign_early_weight
,t1.aging_sign_scantime
,t1.aging_sign_scansitecode
,t1.aging_sign_weight
,t1.aging_sign_scantype
,t1.actual_sign_scantime
,t1.actual_sign_scansitecode
,t1.actual_sign_weight
,t1.actual_sign_scantype
,t2.waybill_status_code
,t2.goods_type_id
,t2.goods_type_code
,t2.goods_type_name
,t2.goods_name
,t2.dispatch_network_code
,t2.first_code
,t2.second_code
,t2.third_code
,t2.is_sign
,t2.sign_time
,t2.is_abnormal
,t2.waybill_source_code
,t2.waybill_source_name
,t2.is_void
,t2.is_refund
,t2.pick_network_code
,t2.pick_network_name
,t2.input_time
,t2.input_network_code
,t2.settlement_weight
,t2.order_source_code
,t2.is_delete
,t2.package_collect_weight
,t2.package_receipt_weight
,t2.waybill_weight
,t2.center_name
,t2.center_code
,t2.ordersource_code
,t2.ordersource_name
,t1.send_earlist_scantime
,t2.customer_code
,t2.customer_name
,t1.first_network_loading_send_oper_next_network_code
,t1.first_network_loading_send_oper_next_network_name
,t1.first_nodal_loading_send_oper_next_network_code
,t1.first_nodal_loading_send_oper_next_network_name
,t1.first_nodal_actual_pre_network_code
,t1.first_nodal_actual_pre_network_type
,t1.first_center_loading_send_oper_next_network_code
,t1.first_center_loading_send_oper_next_network_name
,t1.first_center_actual_pre_network_code
,t1.first_center_actual_pre_network_type
,t1.end_center_loading_send_oper_next_network_code
,t1.end_center_loading_send_oper_next_network_name
,t1.end_center_actual_pre_network_code
,t1.end_center_actual_pre_network_type
,t1.end_nodal_loading_send_oper_next_network_code
,t1.end_nodal_loading_send_oper_next_network_name
,t1.end_nodal_actual_pre_network_code
,t1.end_nodal_actual_pre_network_type
,t1.end_network_actual_pre_network_code
,t1.end_network_actual_pre_network_type
,t1.dt
 from tmp_s03_waybill_trace_mid_dt t1 left join tmp_dwd_oms_waybill_dt t2 on t1.waybill_no = t2.waybill_no ) tmp
 distribute by dt, abs(hash(waybill_no)) % 120
 ;